package cn.edu.upc.letao.dao;

import cn.edu.upc.letao.entity.CommonMember;
import cn.edu.upc.letao.entity.CommonMessage;
import cn.edu.upc.letao.entity.CommonMsgUpOrDown;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

/**
 * Created by 风飞扬 on 2017/4/5.
 * 这是消息(Message)的dao
 */
public interface CommonMessageDao extends CrudRepository<CommonMessage,Integer> {
    //根据池子id查询多条消息  (暂时不使用)
    public  List<CommonMessage> findByUidAndPoolid(int uid,int poolid);

    //根据池子id查询多个消息id
    @Query(value = "select mid from lt_common_message where poolid = ?1",nativeQuery = true)
    public List<Integer> getMessageIdsByPoolid(int poolid);

    //根据消息mid查询唯一消息
    public CommonMessage findByMid(int mid);

    //查询池子top10
    @Query(value = "SELECT * FROM lt_common_message WHERE poolid = ? ORDER BY upnum DESC LIMIT 10",nativeQuery = true)
    public List<CommonMessage> findTop10Msgs(int poolid);

    //查询全部消息 top5  根据收藏人数和赞的人数
    @Query(value = "SELECT * FROM lt_common_message ORDER BY upnum DESC,collectionnum DESC LIMIT 5",nativeQuery = true)
    public List<CommonMessage> findTop5Msgs();

    //查询所有消息
    @Query(value = "SELECT * FROM lt_common_message WHERE text LIKE %?2% limit ?1,20",nativeQuery = true)
    public List<CommonMessage> findPageable(int page,String name);

    //数所有消息
    @Query(value = "SELECT COUNT(*) FROM lt_common_message WHERE text LIKE %?1%",nativeQuery = true)
    public Long countPageable(String name);
}
